<?php
/**
 *
 *
 * @author Zeon <scplay@gmail.com>
 * @date 2018/7/12
 * @copyright PanxSoft Inc.
 */
namespace App\Services\Repositories;


use App\Models\Customer\Customer;
use App\Models\Panx\Visit;
use App\Models\Panx\VisitType;
use Carbon\Carbon;
use Illuminate\Contracts\Cache\Store;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class VisitRepository
{
    protected $visit;

    public function __construct()
    {
        $this->visit = new Visit;
    }

    /**
     * @test 
     *      
     * @param int $store_id
     * @param string $group_type
     * @param array|null|Carbon[2] $time_range
     *
     * @return Visit[]|\Illuminate\Database\Eloquent\Collection
     * @throws \Exception
     */
    public function queryStoreVisits(int $store_id,
                                     string $group_type,
                                     array $time_range = null)
    {

        $group_sql = $this->groupSqlByType($group_type);

        $visit_counts = $this->visit
            ->select(
                // wx user visit count
                DB::raw('COUNT(*) AS count'),

                // created at with group sql
                DB::raw('MAX(created_at) AS end_at'),
                DB::raw('MIN(created_at) AS start_at'),

                // GROUP_CONCAT can implode wx ids default with glue ','
                // 这个函数有上限的，如果使用可能需要修改 mysql.cnf
                // @link https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat
//                DB::raw('GROUP_CONCAT(DISTINCT visitorable_id) as wechat_user_ids'),

                DB::raw($group_sql['select']))
            ->where([
                'hostable_id' => $store_id,
                'hostable_type' => class_basename(Store::class),
                'type' => VisitType::WECHAT_USER_VISIT_STORE,
            ])
            ->groupBy($group_sql['groupBy'])
            ->orderBys($group_sql['orderBys'])
            ->when($time_range, function (Builder $builder, $created_at_range) {
                $builder->whereBetween('created_at', $created_at_range);
            })
            ->get()
        ;

        return $visit_counts;
    }

    /**
     * 后台客户/店铺统计访问数量
     *
     * @param array $store_ids
     * @param string $group_type
     * @param array|null $time_range
     * @return Visit[]|\Illuminate\Database\Eloquent\Collection
     * @throws \Exception
     */
    public function queryCustomerOrStoreVisits(array $store_ids,
                                               String $group_type,
                                               array $time_range = null)
    {

        $group_sql = $this->groupSqlByType($group_type);

        $visit_counts = $this->visit
            ->select(
                DB::raw('COUNT(*) AS count'),
                DB::raw('MAX(created_at) AS end_at'),
                DB::raw('MIN(created_at) AS start_at'),
                DB::raw($group_sql['select']))
            ->where(function(Builder $query) use($store_ids){
                $this->queryStoresVisits($query, $store_ids);
            })
            ->groupBy($group_sql['groupBy'])
            ->orderBys($group_sql['orderBys'])
            ->when($time_range, function (Builder $builder, $created_at_range) {
                $builder->whereBetween('created_at', $created_at_range);
            })
            ->get();

        return $visit_counts;
    }

    /**
     * @param string $group_type
     *
     * @link https://blog.csdn.net/spokenInChina/article/details/49680251 - Mysql日期函数使用汇总
     *
     * @return array
     * @throws \Exception
     */
    private function groupSqlByType(string $group_type): array
    {
        switch (mb_strtolower($group_type)) {
            case 'year' :
                return $this->groupSqlYear();
            case 'month' :
                return $this->groupSqlYearMonth();
            case 'week' :
                return $this->groupSqlYearWeek();
            case 'day' :
                return $this->groupSqlYearMonthDay();
            default :
                throw new \Exception('Invalid group type given');
        }
    }

    private function groupSqlYear(): array
    {
        return $sql = [
            'select'  => "YEAR(created_at) AS year", // year / month / week 不用 `` 也不会报错，什么情况使用关键字会报错呢？
            'groupBy' => "year",
            'orderBys' => [
                ['order_by' => 'year', 'sort' => 'desc']
            ],
        ];
    }

    /**
     * @return array
     */
    private function groupSqlYearMonth(): array
    {
        return $sql = [
            'select'  => "YEAR(created_at) AS `year`, MONTH(created_at) AS `month`",
            // groupBy 多个字段和 distinct 很像，year + month 的组合才合并成一组
            'groupBy' => ['year', 'month'],
            'orderBys' => [
                ['order_by' => 'year', 'sort' => 'desc'],
                ['order_by' => 'month', 'sort' => 'desc']
            ],
        ];
    }

    private function groupSqlYearWeek(): array
    {
        return $sql = [
            'select'  => "YEAR(created_at) AS `year`, WEEK(created_at) AS `week`",
            'groupBy' => ['year', 'week'],
            'orderBys' => [
                ['order_by' => 'year', 'sort' => 'desc'],
                ['order_by' => 'week', 'sort' => 'desc']
            ],
        ];
    }

    private function groupSqlYearMonthDay(): array
    {
        return $sql = [
            'select'  => "YEAR(created_at) AS `year`, MONTH(created_at) AS `month`, DAY(created_at) AS `day`",
            'groupBy' => ['year', 'month', 'day'],
            'orderBys' => [
                ['order_by' => 'year', 'sort' => 'desc'],
                ['order_by' => 'month', 'sort' => 'desc'],
                ['order_by' => 'day', 'sort' => 'desc'],
            ],
        ];
    }

    /**
     * @param Builder $query
     * @param array $store_ids
     * @return Builder
     */
    private function queryStoresVisits(Builder $query, array $store_ids)
    {
        return $query->where([
            'hostable_type' => class_basename(Store::class),
            'type'          => VisitType::WECHAT_USER_VISIT_STORE,
            ])
            ->whereIn('hostable_id', $store_ids);
    }

}